#coding=utf-8
import sys
import time
from tool.dawate_chat import dawate_streaming
import json_repair
from tool.dify_chat_api import dify_chat_streaming
t=time.time()
import logging
from datetime import  datetime
current_time=datetime.now()

import os
# 设置日志
ptah="./out_log/"
if not os.path.exists(ptah):
    os.mkdir(ptah)

logging.basicConfig(level=logging.DEBUG,
                    format='%(asctime)s - %(levelname)s - %(message)s',
                    filename='./out_log/'+current_time.strftime("%Y-%m-%d_%H-%M-%S")+'dawate_log.txt',
                    filemode='a')  # 追加模式
def dify_chat(query):
    # API端点和API密钥
    # url = 'http://172.16.32.45:11206/v1/chat-messages' #v1/chat-messages'调用聊天框  workflows/run智能体
    url = 'http://192.168.73.129:80/v1/chat-messages' #v1/chat-messages'调用聊天框  workflows/run智能体
    api_key = 'app-ldimOhba65Dq5BlHpPXQaDka'  # 替换为您的实际API密钥

    # 请求头
    headers = {
        'Authorization': f'Bearer {api_key}',
        'Content-Type': 'application/json'
    }

    # 请求数据
    data = {
        "inputs": {},
        "query": query,
        "response_mode": "streaming", #blocking streaming
        "conversation_id": "",
        "user": "abc-123",
            }


    # # # 将数据转换为JSON字符串
    # #
    # # # 发送POST请求
    # response = requests.post(url, headers=headers, data=json.dumps(data))
    # # # response.encoding = 'utf-8'
    # # # 打印响应内容
    # print("response.status_code:",response.status_code)
    # print("response.text:",json.loads(response.text))
    response = requests.post(url, headers=headers, data=json.dumps(data))
    # 查看结果
    if response.status_code == 200:
        print('成功')
        logging.info('大模型响应成功')
        # print(json.loads(response.text))
    else:
        print('失败')
        logging.info('大模型响应失败')
        # print(json.loads(response.text))

    return json.loads(response.text)
def dify_chat1(query,conversation_id=''):
    # API端点和API密钥
    # url = 'http://172.16.32.45:11206/v1/chat-messages' #v1/chat-messages'调用聊天框  workflows/run智能体
    url = 'http://192.168.73.129:80/v1/chat-messages'  # v1/chat-messages'调用聊天框  workflows/run智能体
    api_key = 'app-oKletbofahxr5rhn4KHedZ0O'  # 替换为您的实际API密钥

    # 请求头
    headers = {
        'Authorization': f'Bearer {api_key}',
        'Content-Type': 'application/json'
    }

    # 请求数据
    data = {
        "inputs": {},
        "query": query,
        "response_mode": "streaming",  # blocking streaming
        "conversation_id": conversation_id,
        "user": "abc-123",
    }

    # 将数据转换为JSON字符串
    data_json = json.dumps(data)

    # 发送POST请求
    # response = requests.post(url, headers=headers, data=data_json.encode("utf-8"))
    # response.encoding = 'utf-8'

    answer = ''

    # 发送GET请求，并设置stream=True
    with requests.post(url, headers=headers, data=data_json.encode("utf-8"), stream=True) as r:
        # 检查请求是否成功
        r.raise_for_status()

        # 打印每一行文本
        for line in r.iter_lines():
            # 忽略保持连接的空行
            if line:
                # 解码每一行，因为返回的数据可能是字节串
                line = line.decode('utf-8')
                if 'data: ' in line:
                    data = json.loads(line.replace('data: ', ''))
                    if data['event'] == 'message':
                        # print(data['answer'])
                        answer += data['answer']
                        conversation_id=data['conversation_id']
    return answer,conversation_id
def dify_chat2(query,conversation_id=''):
    # API端点和API密钥
    # url = 'http://172.16.32.45:11206/v1/chat-messages' #v1/chat-messages'调用聊天框  workflows/run智能体
    url = 'http://192.168.73.129:80/v1/chat-messages'  # v1/chat-messages'调用聊天框  workflows/run智能体
    api_key = 'app-SpufFzpvwVYUn0AruYB1WmRj   '# 项目分类'app-DtzcELntHBvJ8L8IwAatVZHG'  # 替换为您的实际API密钥

    # 请求头
    headers = {
        'Authorization': f'Bearer {api_key}',
        'Content-Type': 'application/json'
    }

    # 请求数据
    data = {
        "inputs": {},
        "query": query,
        "response_mode": "streaming",  # blocking streaming
        "conversation_id": conversation_id,
        "user": "abc-123",
    }

    # 将数据转换为JSON字符串
    data_json = json.dumps(data)

    # 发送POST请求
    # response = requests.post(url, headers=headers, data=data_json.encode("utf-8"))
    # response.encoding = 'utf-8'

    answer = ''

    # 发送GET请求，并设置stream=True
    with requests.post(url, headers=headers, data=data_json.encode("utf-8"), stream=True) as r:
        # 检查请求是否成功
        r.raise_for_status()

        # 打印每一行文本
        for line in r.iter_lines():
            # 忽略保持连接的空行
            if line:
                # 解码每一行，因为返回的数据可能是字节串
                line = line.decode('utf-8')
                if 'data: ' in line:
                    data = json.loads(line.replace('data: ', ''))
                    if data['event'] == 'message':
                        # print(data['answer'])
                        answer += data['answer']
                        conversation_id=data['conversation_id']
    return answer,conversation_id

import pymysql


def db_connet():
    conn = pymysql.connect(
        host='127.0.0.1',  # 连接名称，默认127.0.0.1
        user='root',  # 用户名
        passwd='123456',  # 密码
        port=3306,  # 端口，默认为3306
        db='zentao',  # 数据库名称
        charset='utf8',  # 字符编码
    )
    print("----数据库连接成功------", conn)
    logging.info("----数据库连接成功------ %s", conn)
    return conn
def search_db():
    import datetime
    # 获取当前时间
    now = datetime.datetime.now()
    f_path = './data_out/'
    if not os.path.exists(f_path):
        os.mkdir(f_path)
    # 将时间格式化为字符串，例如：2025-03-11_14-30-00
    file_name = now.strftime(f_path + "output_%Y-%m-%d_%H-%M-%S.txt")

    # 使用格式化后的文件名打开文件
    file = open(file_name, 'w', encoding='utf-8')
    # file=open('./data_out/14-4项目业绩output.txt', 'w', encoding='utf-8')
    # 获取游标
    conn = db_connet()
    cursor = conn.cursor()

    # 指定要查询的表格列表
    # table_names = [
    #     'ex_project',
    #     'ex_file'
    # ]
    table_names = {
        'a_biz_process': '任务处理流程表',
        'a_business_template': '业务模板表',
        "a_chat_log": "聊天日志表",
        " a_permissions": '角色权限表',
        "a_security_policies": '安全策略表',
        "zt_task": '任务表',
    }

    # 构造SQL查询，获取指定表的表名、字段名和字段备注
    # sql = """
    # SELECT TABLE_NAME, COLUMN_NAME, COLUMN_COMMENT
    # FROM INFORMATION_SCHEMA.COLUMNS
    # WHERE TABLE_SCHEMA = 'zentao-old0.1' AND TABLE_NAME IN ({})
    # """.format(','.join(['%s'] * len(table_names))
    # )

    sql = """
        SELECT 
        t.TABLE_NAME, 
        t.TABLE_COMMENT, 
        c.COLUMN_NAME, 
        c.COLUMN_COMMENT,
        k.REFERENCED_TABLE_NAME, 
        k.REFERENCED_COLUMN_NAME,
        k.CONSTRAINT_NAME
    FROM 
        INFORMATION_SCHEMA.TABLES t
    JOIN 
        INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
    LEFT JOIN 
        INFORMATION_SCHEMA.KEY_COLUMN_USAGE k ON c.TABLE_NAME = k.TABLE_NAME 
        AND c.COLUMN_NAME = k.COLUMN_NAME AND c.TABLE_SCHEMA = k.TABLE_SCHEMA
        AND k.REFERENCED_TABLE_NAME IS NOT NULL
    WHERE 
        t.TABLE_SCHEMA = 'zentao' AND t.TABLE_NAME IN ({})

        """.format(','.join(['%s'] * len(table_names)))

    try:
        # 执行SQL查询
        cursor.execute(sql, list(table_names.keys()))
        results = cursor.fetchall()
        # print("results:",results) #tuple  (('zt_task', 'id', '编号'), ('zt_task', 'project', '项目'),
        dict_tem = {}
        for table_name, table_comment, tb_colum, colum_comment, ref_table, ref_column, constraint_name in results:
            if table_name not in dict_tem:
                dict_tem[table_name] = {
                    'table_comment': table_comment,
                    'columns': [],
                    'foreign_keys': []
                }
            dict_tem[table_name]['columns'].append(f"{tb_colum}({colum_comment})")
            if ref_table:
                # dict_tem[table_name]['foreign_keys'].append({
                #     'constraint_name': constraint_name,
                #     'column_name': tb_colum,
                #     'referenced_table': ref_table,
                #     'referenced_column': ref_column
                # })
                dict_tem[table_name]['foreign_keys'].append({
                    '约束名称': constraint_name,
                    '字段名称': tb_colum,
                    '引用表名': ref_table,
                    '引用字段': ref_column
                })
        print(dict_tem)
        logging.info('dict_tem:%s', dict_tem)
        for key, values in dict_tem.items():
            file.writelines(key + "（" + values["table_comment"] + "）" + '\n')
            file.writelines('表字段：' + ','.join(values["columns"]) + '\n')
            for dict in values["foreign_keys"]:
                file.writelines('表外键：' + str(dict) + '\n')
            file.writelines('\n')
    finally:
        # 关闭游标和连接
        cursor.close()
        conn.close()
    return dict_tem
def write_json(filename, new_data):
    # 读取现有数据
    data = read_json(filename)

    # 检查新数据是否已存在于现有数据中
    for new_item in new_data:
        if new_item not in data:
            data.append(new_item)

    # 将数据转换为JSON格式并写入文件
    with open(filename, 'w', encoding='utf-8') as file:
        json.dump(data, file, ensure_ascii=False, indent=4)


def read_json(filename):
    # 检查文件是否存在，并读取现有数据（如果存在）
    if os.path.exists(filename):
        with open(filename, 'r', encoding='utf-8') as file:
            try:
                data = json.load(file)
            except json.JSONDecodeError:
                data = []  # 如果文件不是有效的JSON，则开始一个新的列表
    else:
        data = []  # 如果文件不存在，则开始一个新的列表

    return data
def data_base_connet_error(sql_statement):
    # 获取游标
    conn=db_connet()
    cursor = conn.cursor()
    print(cursor)

    # 执行sql语句execute和executemany
    # 定义要执行的SQL语句列表
    try:
        cursor.execute(sql_statement)
        hearders = [description[0] for description in cursor.description]
        results = cursor.fetchall()

        # print(results)

        cursor.close()  # 关闭查询游标
        conn.commit()  # 事务的提交
        conn.close()  # 查询完毕，需要关闭连接，释放计算机资源
        # print('sql执行成功')

        return hearders, results
    except pymysql.MySQLError as e:
        print(f"An error occurred: {e}")
        cursor.close()  # 关闭查询游标
        conn.commit()  # 事务的提交
        conn.close()  # 查询完毕，需要关闭连接，释放计算机资源
        # print('sql执行成功')
        return e,None
def data_base_connet(sql_statement):
    conn = db_connet()
    cursor = conn.cursor()

    try:
        # 执行SQL语句
        cursor.execute(sql_statement)

        # 如果是查询语句，则获取结果
        if sql_statement.strip().lower().startswith('select'):
            headers = [desc[0] for desc in cursor.description]
            results = cursor.fetchall()
        else:
            # 如果是非查询语句，只返回影响行数或空结果
            headers = ['rows_affected']
            results = [[cursor.rowcount]]
            conn.commit()  # 提交事务

        cursor.close()
        conn.close()

        return headers, results

    except pymysql.MySQLError as e:
        print(f"An error occurred: {e}")
        conn.rollback()  # 出错回滚
        cursor.close()
        conn.close()
        return None, None

import requests
import json
import re
def re_str(str):
    # 调整正则表达式以包含换行符和任意空白字符
    # pattern_including_newline = r'(.*?)\s*表字段'
    pattern_including_newline = r'(.*?)\s*（'
    # 使用re.DOTALL标志，使点号匹配包括换行符在内的任意字符
    match_including_newline = re.search(pattern_including_newline, str, re.DOTALL)

    # 提取匹配的内容
    result_including_newline = match_including_newline.group(1).strip() if match_including_newline else None
    return result_including_newline
def dify_searchdb():
    dict_new={}
    api_key = 'dataset-Nx874tLa6L3Yc92jWRnpEw1G'  # 替换为您的实际API密钥
    dataset_id="2e81b473-edd0-489e-b4b4-b8ae12f7c910"
    document_id='0addf117-7a48-42f2-94f2-9ed77ba70c7e'
    url =  f'http://192.168.73.129:80/v1/datasets/{dataset_id}/documents/{document_id}/segments'  # v1/chat-messages'调用聊天框  workflows/run智能体

    headers = {
        'Authorization': f'Bearer {api_key}',
        'Content-Type': 'application/json'
    }



    # 发送POST请求
    response = requests.get(url, headers=headers)
    response_data=json.loads(response.text)
    s=response_data.get('data')
    for i in s:
        for key,value in i.items():
            # print('key:',key,'values:',value)
            # if key=='position':
            #     # print('key:',key,'values:',value)
            if key=='content':
                # print('key:', key, 'values:', value)
                v_str=re_str(value)
                # print('v_str:',v_str)
                dict_new[v_str]=value

    return response,dict_new


def main():
    t = time.time()
    # dict_tem=search_db()
    response, dict_tem = dify_searchdb()  # 知识库已经存在 通过文本创建文档
    print('dict_new:', dict_tem)

    query = input("请输入你的问题：")

    print('----------大模型提示词：---------', str(dict_tem) + str(query))
    logging.info('----------大模型提示词：---------%s', str(query))
    print('------用户需要查询的问题为：\n', query)
    logging.info('------用户需要查询的问题为：\n%s', query)
    # data_answ=dify_chat(query,str(dict_tem)+str(table_connect))
    data_answ = ""
    count = 0  # 初始化计数器
    while data_answ is "" and count < 3:
        data_answ = dify_chat_streaming(str((query)))  # 找出表
        count += 1
    if count == 3 and data_answ is "":  # 检查是否达到十次且dataansw仍为空
        print("请重试")  #
    print("data_answ:", data_answ)

    print("-------step1:大模型找出的表结果：\n", data_answ)
    logging.info("-------step1:大模型回答结果：\n %s", str(data_answ))
    if 'json' in str(data_answ):
        pattern1 = r"```json(.*?)```"
        match = re.search(pattern1, data_answ, re.DOTALL)
        json_statement = match.group(1).strip()
        print('找到的相关的表有：', json_statement)
    else:
        print('不存在相关的表格。', data_answ)
        sys.exit()
    list_data = {}  # 需要的表结构
    for key, value in json.loads(json_statement).items():
        # list_data[key.lower()]=dict_tem[key.lower()]
        list_data[key] = dict_tem[key]
    print("list_data:", list_data)
    sql_data_answ,conversation_id = dify_chat1('表结构：' + str(list_data) + '问题：' + str((query)))  # 编写sql
    pattern = r"```sql(.*?)```"
    match = re.search(pattern, sql_data_answ, re.DOTALL)
    user_questions = [{}]

    if match:
        print("\n-------step2查到的对应的sql语句为：\n")
        sql_statement = match.group(1).strip()
        print("\n-------step2查到的对应的sql语句为：\n", sql_statement)
        logging.info("\n-------step2查到的对应的sql语句为：\n%s", sql_statement)
        if sql_statement:
            hearders, results = data_base_connet(sql_statement)
            if results is not None:
                user_questions[0]['问题'] = query
                user_questions[0]['SQL'] = sql_statement
                print('\n-------step3:sql执行结果：\n')
                logging.info('\n-------step3:sql执行结果：\n')
                # print("results:\n",results)
                logging.info('%s', hearders)
                print(hearders)
                for i in results:
                    print(i)
                    logging.info(i)

            else:
                print("------sql生成错误正在重新生成-----")
                logging.info("------sql生成错误-----")
                pattern2 = r"```sql(.*?)```"
                sql_error = hearders
                sql_data_answ, conversation_id= dify_chat1('表结构：' + str(list_data) + '问题：' + str((query)) + str(sql_error),conversation_id=conversation_id)
                print('大模型生成内容：',sql_data_answ)
                match = re.search(pattern2, sql_data_answ, re.DOTALL)
                if match:
                    print("\n-------step2查到的对应的sql语句为：\n")
                    sql_statement = match.group(1).strip()
                    print("\n-------step2查到的对应的sql语句为：\n", sql_statement)
                    logging.info("\n-------step2查到的对应的sql语句为：\n%s", sql_statement)
                    if sql_statement:
                        hearders, results = data_base_connet(sql_statement)
                        if results is not None:
                            user_questions[0]['问题'] = query
                            user_questions[0]['SQL'] = sql_statement
                            print(hearders)
                            for i in results:
                                print(i)
                                logging.info(i)
                        else:
                            print('SQL查询报错：', results)

    # 将sql暂时保存到本地
    # 创建目录（如果不存在）
    if not os.path.exists('../out_json/'):
        os.mkdir('../out_json/')

    filename = '../out_json/user_questions.json'
    print(f'耗时：{time.time() - t}')
    data = read_json(filename)
    print("现有数据:", data)

    # 添加新数据并写入文件
    write_json(filename, user_questions)

    # 再次读取并打印数据
    data = read_json(filename)
    print("更新后的数据:", data)

    '''
    说明：该版本 优化：
    sql回答错误 会接着历史记录接着问

    '''
def split_text_into_segments(text_list, max_length=10000):
    """
    Splits a list of text into segments, each with a maximum length of 4000 characters.
    If a text segment and the next one combined exceed 4000 characters, the current segment is used alone.
    If the current segment and the next two combined do not exceed 4000 characters, they are merged into one segment.

    :param text_list: List of text strings
    :param max_length: Maximum length of each segment
    :return: A list of text segments
    """
    segments = []
    i = 0
    while i < len(text_list):
        current_text = text_list[i]
        if len(current_text) > max_length:
            # If the current text is longer than max_length, add it as a separate segment
            segments.append(current_text)
            i += 1
        else:
            # Check the length of the next text
            if i + 1 < len(text_list):
                next_text = text_list[i + 1]
                combined_length = len(current_text) + len(next_text)
                if combined_length > max_length:
                    # If the combined length of current and next text exceeds max_length, add current text as a segment
                    segments.append(current_text)
                    i += 1
                else:
                    # Check if the next two texts can be combined
                    if i + 2 < len(text_list):
                        next_next_text = text_list[i + 2]
                        combined_length_with_next_next = combined_length + len(next_next_text)
                        if combined_length_with_next_next <= max_length:
                            # If the combined length of current, next, and next_next texts does not exceed max_length, merge them
                            segments.append(current_text + next_text + next_next_text)
                            i += 3
                        else:
                            # Otherwise, merge current and next text
                            segments.append(current_text + next_text)
                            i += 2
                    else:
                        # If there's no next_next text, merge current and next text
                        segments.append(current_text + next_text)
                        i += 2
            else:
                # If there's no next text, add the current text as a segment
                segments.append(current_text)
                i += 1
    return segments
def slice_text(text, slice_length):
    slices = []
    slices_new=[]
    current_slice = ""
    current_length = 0
    title = ''
    if ':' in text:
        title = text.split('：')[0]
    for char in text:
        current_slice += char
        current_length += 1

        # 当当前切片长度接近1000时，开始寻找分割点
        if current_length >= slice_length:
            # 查找最近的）》号或上一个标点符号
            split_point = re.search(r'》|[^，。！？；：]', current_slice[::-1])
            if split_point:
                # 获取分割点的真实位置
                split_index = len(current_slice) - split_point.start()
                # 添加切片到列表
                slices.append(current_slice[:split_index])
                # 重置当前切片和长度计数器
                current_slice = current_slice[split_index:]
                current_length = len(current_slice)

    # 添加最后一个切片
    if current_slice:
        slices.append(current_slice)
    for i, slice in enumerate(slices):
        if i == 0:
            print(f"切片{i + 1}：长度为：{len(slice)}{slice}\n")
            slices_new.append(slice)
        else:
            print(f"切片{i + 1}：长度为：{len(slice)}{title + slice}\n")
            slices_new.append(title + slice)
    return slices_new
def split_list_into_chunks(lst, chunk_size=5000):
    """
    Splits a list of strings into chunks of a specified size, ensuring that individual strings are not split.

    :param lst: List of strings to be split.
    :param chunk_size: Maximum size of each chunk.
    :return: List of chunks.
    """
    chunks = []
    current_chunk = []

    for item in lst:
        # If adding this item to the current chunk exceeds the chunk size, start a new chunk
        if len(item)>chunk_size:
            print('长度超长了：',len(item))
            slices = slice_text(item,chunk_size)
            for s_i in slices:
                current_chunk.append(s_i)
        if sum(len(x) for x in current_chunk) + len(item) > chunk_size:
            chunks.append(''.join(current_chunk))
            current_chunk = [item]
        else:
            current_chunk.append(item)

    # Add the last chunk if it's not empty
    if current_chunk:
        chunks.append(''.join(current_chunk))

    return chunks
def biz_td(data,user_data):
    user_input=user_data[0]
    intention_user=user_data[1]
    user_account=user_data[2]
    if intention_user=='查询任务':
        sql_statement = f'''
        select * from zt_task where openedBy='{user_account}'
            '''
        hearders, results = data_base_connet(sql_statement)
        print(f"你可以查询的任务有：\n{hearders}")
        logging.info("task Headers: %s", hearders)
        task_read_template='''
        查询任务名称：xxxx,
        查询字段：所有字段/xxx
        '''
        for i in results:
            print(i)
            logging.info("task results_i: %s", i)

        print('\n你需要查询哪条数据？模板展示')
        print(task_read_template)
    elif intention_user == '创建任务':
        sql_1='''
                    SELECT realname 
            FROM zt_user 
            WHERE account IN (
                SELECT account 
                FROM zt_team 
                WHERE root = '1894'
            );
        '''
        hearders1, results1 = data_base_connet(sql_1) #获取指定项目的团队成员
        sql2='''
        select content  from agent_template where full_name="/zentao/task/create"
        '''
        hearders2, results2 = data_base_connet(sql2)
        print('创建任务模板为:')
        print(hearders2)
        results2=[i[0].replace('张三/李四','/'.join([i[0] for i in results1])) for i in results2]
        for i in results2:
            print(i)
        sql_statement='''
                SELECT 
    COLUMN_NAME AS '字段名称',
    COLUMN_COMMENT AS '字段备注'
FROM 
    INFORMATION_SCHEMA.COLUMNS 
WHERE 
    TABLE_NAME = 'zt_task'
    AND TABLE_SCHEMA = 'zentao'; 
 
        '''
        hearders, results = data_base_connet(sql_statement)


        task_create_='''
                INSERT INTO zt_task (
            project, parent, execution, module, design, story, storyVersion, designVersion,
            fromBug, feedback, fromIssue, name, type, difficulty, mode, pri
        ) VALUES (
            1310, 0, 1486, 0, 0, 0, 1, 1,0, 0, 0, '管理体系框架-新建', '任务类型', 'general', '', 1
        );
         注意：| 任务难度*   | veryImportant非常重要，important重要，general一般,notImportant不重要  |
            | 任务优先级* | 1(优先级最高)，2，3，4  |
        '''
        task_create_template=results2
        # 任务表必填字段为：
        # 请你按照模板来创建任务：
        #     | 属性       | 值   |
        #     |------------|------|
        #     | 任务名称*  | xxx  |
        #     | 任务类型*   | xxx  |
        #     | 任务难度*   | veryImportant非常重要，important重要，general一般,notImportant不重要  |
        #     | 任务优先级* | 1(优先级最高)，2，3，4  |
        #
        #         '''
        # print(task_create_template)
        user_input=input('\n用户输入问题：')
        tem_prompt=f'''
          安全策略为：{Security_Identification()};
                  当前用户的用户权限为：{data}；
                  当前用户的操作意图为：{intention_user}
                  用户问题为：{user_input};
                  模板为：{task_create_template}'''
        print("安全策略权限过滤：",len(tem_prompt))
        json_statement = json.loads(json_process(
            dify_chat_streaming(tem_prompt, data_event='message', api_key='app-kq9Bj0KXAPNjYKUyZd0gNoYJ')))

        st=json_statement#.get("是否合规")
        print('\n用户的操作合规性判断：'+st)
        if st.get('是否合规')=='是':
            SQL_prompt=f'''
            要求：
            1.基于用户问题进行sql生成；
            2.创建任务时请参考模板来进行sql创建；
            3。判断sql字段是否与模板中的字段名称一致；判断字段值是否符合要求；
            zt_task表中， project, parent, execution, module, design, story, storyVersion, designVersion,
            fromBug, feedback, fromIssue这些字段都是固定的，就按照模板即可，其余字段根据用户要求即可。
            模板{task_create_}
            用户问题为：{user_input}，
            数据库表字段为：{hearders}{results}
                '''
            answer=dawate_streaming(SQL_prompt).split('</think>')[-1]
            print('生成的sql为：',answer)
            sql_statement_=json_process(answer)
            headers, results = data_base_connet(sql_statement_)
            print('数据库受影响行数:', headers, results)
            print('完成创建任务')
        else:
            print('输入不合规')

    elif intention_user == '普通问答':
        data_answ = dawate_streaming('用户问题为：' + (user_input))
        print(data_answ)
    # if results is not None:
        # print(hearders)
        # for i in results:
        #     print(i)
        #
        # print('输入数据长度',len(str(hearders) +str(results)))
        # query ='找出信息化相关的项目,,并以json的格式返回项目名称'
        # data_answ = ''
        # while data_answ is "":
        #     answ, conversation_id = dify_chat2('表内容：' + str(hearders) +str(results)+ '问题：' + str((query)))
        # print('\n大模型结果：\n', answ)
        # if not os.path.exists('../out_json/'):
        #     os.mkdir('../out_json/')

        # filename = '../out_json/user_项目识别情况1.txt'
        # file_=open(filename,'w',encoding='utf-8')

        # results_new=[str(i) for i in results]
        # split_segments_tem = split_list_into_chunks(results_new, chunk_size=5000)
        # split_segments = [str(hearders) + str(i) for i in split_segments_tem]
        # query = input('请输入你的问题:')
        # query='找出中级以上的用户有哪些'
        # conversation_id=''
        # while True:
        #     user_input=input('用户输入要求：')
            # user_input='''
            # 要求：1、项目固定服务技术团队3人（含）以上，团队少于3人的得0分，3人以上得2分；2、项目经理具备CISP证书或信息系统项目集成工程师，均不具备的得0分，具备其中1证的得3分，都具备的得5分；3、服务工程师均具备CCNP或HCNP或H3CNE证书，有1人不具备的得0分，均具备1-2证的得3分，均具备2证以上的得5分；4、项目经理具有3年以上信息安全工作经验，服务工程师均具有1年以上的信息安全项目运维实施工作经验，不满足条件的得0分，满足1项条件的得2分，均满足条件的得3分。
            #
            # '''
            # if '再见' in user_input:
            #     data_answ, conversation_id = dify_chat2( '要求：' + str((user_input)),conversation_id=conversation_id)
            #     print('再见：',data_answ)
            #     break
            # else:
            #     answer_merge=[]
            #     prompt_1='''
        #         提示词：帮我进行项目经理以及团队成员推荐，并对团队进行评分，并且根据证书级别对用户进行归类。
        #         并以json格式输出：{"推荐项目经理为"：[{"用户id","xxx","姓名"："xxx"},
        #                                         {"用户id","xxx","姓名"："xxx"}]，
        #                         "推荐团队成员为"：[{"用户id"："xxx"，"姓名"："xxx","拥有证书"："xxx"},
        #                                         {"用户id"："xxx"，"姓名"："xxx","拥有证书"："xxx"}],
        #                         "团队评分为"："xxx分",
        #                         "中级证书成员有"：[{"用户id"："xxx"，"姓名"："xxx","拥有证书"："xxx"},
        #                                         {"用户id"："xxx"，"姓名"："xxx","拥有证书"："xxx"}],
        #                         "高级证书成员有"：[{"用户id"："xxx"，"姓名"："xxx","拥有证书"："xxx"},
        #                                         {"用户id"："xxx"，"姓名"："xxx","拥有证书"："xxx"}]
        #                                         }
        #
        #
        #         '''
        #         for index,cont_i in enumerate(split_segments):
        #             print('表格切分后的内容：',len(str(cont_i)),cont_i)
        #             data_answ, conversation_id = dify_chat2('数据为：' + str(cont_i) + str((user_input))+str(prompt_1),conversation_id=conversation_id)
        #                 # file_.writelines(data_answ + '\n')
        #             print(f'\n------结果{index}：--------\n',data_answ)
        #             answer_merge.append(data_answ)
        #         print('-------模型输出结果：-------',answer_merge)
        #         data_answ, conversation_id = dify_chat2('将结果进行合并,并以json格式输出,原始的json格式不要变。'+str(answer_merge),
        #                                                 conversation_id=conversation_id)
        #         print('-----结果合并后的结果------',data_answ)
        #         text='''
        #         我司承诺已完全理解且满足招标文件中的项目组织人员要求、团队规模及项目组成员资质。我司拟组建一支18人专业服务团队，其中9人具有软件类中级及以上证书（具体人员名单：肖建毅、何宇新、许文武、江雄、陈竞、陈耀冲、黄亮、王宇平、龙江喜），且我司有完善合理的团队稳定保障措施（详见14.3）。另外，项目组成员100%人员都具备信息系统的开发或实施经验，熟悉项目范围内的系统情况或同类型系统，并从事信息系统工作经验一年以上，能熟练胜任所负责工作。
        #         '''
        #
        #         data_answ, conversation_id = dify_chat2(
        #             '要求：基于以上合并的结果，参考以下内容进行文本编写，其中：项目成员包括：项目经理及其团队成员，人员及其数量以实际数据为准。参考样例：'+ str(text),
        #             conversation_id=conversation_id)
        #         print('-----文本编写的结果------', data_answ)
        # # print(f'大模型识别结果保存在{filename}')
        # #     print('\n大模型结果：\n',answ)
def json_process(data_answ):
    pattern = r'```json(.*?)```'
    matches = re.findall(pattern, data_answ, re.DOTALL)  # 抽取出内容并判断长度
    if matches:
        c_statement = matches[-1]
        # print("\n-------step2查到的对应的内容为：\n", c_statement)
    else:
        c_statement = ''
    # decoded_object = json_repair.loads(c_statement)
    # return json.dumps(decoded_object,ensure_ascii=False)
    return c_statement
def intention_recognition(user_input):
    json_statement = json.loads(json_process(
        dify_chat_streaming(user_input, data_event='message', api_key='app-B1JNuJjuhFC2HHpzW2zqwPHE')))
    return json_statement.get('意图')
def filter_user_permissions(user_role):
    hearders=None
    results=None
    if user_role not in ['user','admin']:
        print('当前用户无权限')
    else:
        sql_statement = f'''
                SELECT * FROM a_permissions WHERE role = '{user_role}';
                '''
        hearders, results = data_base_connet(sql_statement)
    print('当前用户权限为：')
    print(hearders)
    for i in results:
        print(i)
    return hearders,results
def Security_Identification():
    Security_prompt='''
    安全策略过滤：
    要求：
    1。恶意指令，对于危害系统的操作直接禁止，阻断执行,严格禁止的系统的操作行为包括以下内容（不限于以下内容）：
    （1）"删除所有用户" ;
    (2)删除所有数据;
    (3)清空数据库;
    (4)修改所有用户的密码;
    (5)将所有用户设置为管理员;
    (6)向数据库中插入大量无效/垃圾数据;
    (7)随机修改数据库记录;
    (8)删除核心系统文件;
    (9)导出所有用户的敏感信息（如邮箱、电话、住址）” / “列出所有管理员账户
    2.越权操作，动态权限校验，用户只能操作自己拥有的权限，越权直接拒绝；
    3.数据泄露，自动脱敏，查询结果中身份证号显示为****；
    
    4.注入攻击，SQL语法树分析，检测DROP TABLE语句特征
    '''

    return Security_prompt
def Business_Domain_Intent_Recognition(user_input):
    json_statement=json.loads(json_process(dify_chat_streaming(user_input,data_event='message',api_key = 'app-xVvAg9zhpVHIeTnXpgzURc3l')))

    return json_statement.get('业务域意图'),json_statement.get('提示语')
if __name__=="__main__":
    #导出数据库
    # search_db()
    # #ste0 用户登陆后权限过滤
    user_role='user'
    hearders,results=filter_user_permissions(user_role)

    # #业务域——场景 智能体意图识别
    user_input = input('\n用户输入问题：')
    Business_Domain_Intent,domain_prompt=Business_Domain_Intent_Recognition(user_input)
    print('Business_Domain_Intent',Business_Domain_Intent)
    # #模板
    #
    # # 用户意图识别
    # user_input = input('\n用户输入问题：')
    if "禅道" in Business_Domain_Intent:
        intention_user=intention_recognition(user_input)
        # 将结果写入日志
        logging.info("User Role: %s", user_role)
        logging.info("Headers: %s", hearders)
        logging.info("Permissions Results: %s", results)
        logging.info("Recognized User Intention: %s", intention_user)

        #step2 意图为：任务相关的
        user_account = 'huangzhan'
        biz_td([hearders,results],[user_input,intention_user,user_account])
        #
    else:
        print('不在业务范围之内')


    # #风险安全识别
    # Security_Identification()


#     sql = """
#     INSERT INTO zt_task (
#     project, parent, execution, module, design, story, storyVersion, designVersion,
#     fromBug, feedback, fromIssue, name, type, difficulty, mode, pri
# ) VALUES (
#     1310, 0, 1486, 0, 0, 0, 1, 1, 0, 0, 0, '文本内容生成', '任务类型', 'general', '', 1
# );
#     """
    # headers, results = data_base_connet(sql)
    # print('数据库受影响行数:',headers[0],results[0][0])



   # query='你是谁'
   # data_answ,conversation_id= dify_chat_streaming(str(query))
   # print(data_answ,conversation_id)